<?php

namespace app\modules\merchant\services;

use app\common\services\BaseService;
use app\config\Constants;
use app\models\GoodsOrder;
use app\models\Merchant;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use yii\helpers\ArrayHelper;
use yii\helpers\Json;

class SettleService extends BaseService{

    public static function download($status,$start_time,$end_time){
        /**
         * @var Merchant $merchant
         */
        $merchant = \Yii::$app->getModule("merchant")->get("user")->getIdentity();
        $query = GoodsOrder::find()->asArray(true)
            ->andWhere(['merchant_id'=>$merchant->getId()])
            ->andWhere(['status'=>$status]);

        if($status == Constants::ORDER_COMPLETE){
            $query->andWhere(['>=',"date_format(end_time,'%Y-%m-%d')",$start_time])
                ->andWhere(['<=',"date_format(end_time,'%Y-%m-%d')",$end_time]);
        }else{
            $query->andWhere(['>=',"date_format(add_time,'%Y-%m-%d')",$start_time])
                ->andWhere(['<=',"date_format(add_time,'%Y-%m-%d')",$end_time]);
        }
        $orders = $query->all();

        //商品分类统计
        $cat_statis = [];

        $spreadsheet = new Spreadsheet();

        //////////////////////明细单/////////////////////////////
        $sheet = $spreadsheet->createSheet(0);
        $sheet->setTitle("明细表");
        //默认列宽和行高
        $sheet->getDefaultRowDimension()->setRowHeight(20);
        $sheet->getDefaultColumnDimension()->setWidth(14);
        //日期和订单号、联系电话、地址、商品名称列宽
        $sheet->getColumnDimension("A")->setWidth(24);
        $sheet->getColumnDimension("B")->setWidth(24);
        $sheet->getColumnDimension("E")->setWidth(20);
        $sheet->getColumnDimension("F")->setWidth(35);
        $sheet->getColumnDimension("G")->setWidth(40);
        //商户名称列合并
        $sheet->mergeCells("B1:D1");
        //统计表头行高
        $sheet->getRowDimension(1)->setRowHeight(20);
        $sheet->getRowDimension(2)->setRowHeight(20);
        $sheet->getRowDimension(3)->setRowHeight(20);
        $sheet->getRowDimension(4)->setRowHeight(20);
        $sheet->getRowDimension(5)->setRowHeight(29);
        $sheet->getRowDimension(6)->setRowHeight(20);
        //统计表头垂直居中
        $sheet->getStyleByColumnAndRow(1,1,2,4)
            ->getAlignment()
            ->setVertical(Alignment::VERTICAL_CENTER);
        //设置统计表头
        $sheet->setCellValue("A1","商户名称");
        $sheet->setCellValue("A2","日期");
        $sheet->setCellValue("A3","交易额");
        $sheet->setCellValue("A4","交易笔数");
        //表头内容
        $sheet->setCellValue("B1",$merchant->merchant_name);
        $sheet->setCellValue("B2",($start_time == $end_time) ? $start_time : $start_time . "-" . $end_time);
        $sheet->getCell("B3")->setValue(!empty($orders) ? array_sum(ArrayHelper::getColumn($orders,"total_price")) : 0)->getStyle()->getNumberFormat()->setFormatCode(NumberFormat::builtInFormatCode(4));
        $sheet->setCellValue("B4",count($orders));
        //表格标题
        $sheet->setCellValue("A5","订单明细");
        $sheet->setCellValue("A6","订单日期");
        $sheet->setCellValue("B6","订单号");
        $sheet->setCellValue("C6","订单金额");
        $sheet->setCellValue("D6","联系人");
        $sheet->setCellValue("E6","联系电话");
        $sheet->setCellValue("F6","收货地址");
        $sheet->setCellValue("G6","商品名称");
        $sheet->setCellValue("H6","数量");
        $sheet->setCellValue("I6","重量");
        $sheet->setCellValue("J6","价格");

        $i=7;
        foreach($orders as $v){
            $sheet->setCellValue("A".$i,$v['add_time']);
            //设置订单号为文本格式
            $sheet->setCellValueExplicit("B".$i,$v['order_no'],DataType::TYPE_STRING2);
            $sheet->getCell("C".$i)->setValue($v['total_price'])->getStyle()->getNumberFormat()->setFormatCode(NumberFormat::builtInFormatCode(4));
            $sheet->setCellValue("D".$i,$v['name']);
            $sheet->setCellValue("E".$i,$v['mobile']);
            $sheet->setCellValue("F".$i,$v['address']);
            foreach(Json::decode($v['goods_item']) as $vv){
                $goods_name = $vv['goods_name'];
                if(isset($vv['attr_names'])){
                    $goods_name .= "(";
                    foreach($vv['attr_names'] as $vvv){
                        $goods_name .= $vvv . '-';
                    }
                    $goods_name = rtrim($goods_name,'-');
                    $goods_name .= ")";
                }
                //设置每行高度
                $sheet->getRowDimension($i)->setRowHeight(20);
                $sheet->setCellValue('G'.$i,$goods_name);
                $sheet->setCellValue('H'.$i,$vv['new_count']);
                $sheet->setCellValue('I'.$i,$vv['weight']);
                $sheet->setCellValue('J'.$i,$vv['price']);
                $i++;

                if(isset($cat_statis[$goods_name])){
                    $cat_statis[$goods_name]['count'] += $vv['new_count'];
                }else{
                    $cat_statis[$goods_name]['weight'] = $vv['weight'];
                    $cat_statis[$goods_name]['count'] = $vv['new_count'];
                }
            }
            $i++;
        }

        //合并订单明细单元格
        $sheet->mergeCells("A5:I5");
        //订单明细字体加粗
        $sheet->getCell("A5")->getStyle()
            ->getFont()
            ->setBold(true);
        //表格内容居中
        $sheet->getStyleByColumnAndRow(1,5,10,$i)
            ->getAlignment()
            ->setHorizontal(Alignment::HORIZONTAL_CENTER)
            ->setVertical(Alignment::VERTICAL_CENTER);

        //////////////////////统计单/////////////////////////////

        $sheet = $spreadsheet->createSheet(1);
        $sheet->setTitle("统计单");

        //默认列宽和行高
        $sheet->getDefaultRowDimension()->setRowHeight(20);
        $sheet->getDefaultColumnDimension()->setWidth(14);
        //设置表头
        $sheet->setCellValue("A1","商品名称");
        $sheet->setCellValue("B1","重量");
        $sheet->setCellValue("C1","数量");
        //设置表头行高
        $sheet->getRowDimension(1)->setRowHeight(20);
        //商品名称列宽
        $sheet->getColumnDimension("A")->setWidth(40);

        $i = 2;
        foreach ($cat_statis as $k => $v) {
            $sheet->getRowDimension($i)->setRowHeight(20);
            $sheet->setCellValue("A".$i,$k);
            $sheet->setCellValue("B".$i,$v['weight']);
            $sheet->setCellValue("C".$i,$v['count']);
            $i++;
        }

        //表哥内容居中
        $sheet->getStyleByColumnAndRow(1,1,3,$i)
            ->getAlignment()
            ->setHorizontal(Alignment::HORIZONTAL_CENTER)
            ->setVertical(Alignment::VERTICAL_CENTER);

        $writer = IOFactory::createWriter($spreadsheet,"Xlsx");
        $writer->save('php://output');
    }
}